package com.hk.commons.poi.excel.read.sax.v07;

import com.hk.commons.poi.excel.exception.ExcelReadException;
import com.hk.commons.poi.excel.exception.InvalidCellReadableExcelException;
import com.hk.commons.poi.excel.metadata.*;
import com.hk.commons.poi.excel.read.sax.SaxAbstractExcelReadExecutor;
import com.hk.commons.poi.excel.read.sax.constants.ExcelXmlConstants;
import com.hk.commons.util.CollectionUtils;
import com.hk.commons.util.StringUtils;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.SneakyThrows;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.util.XMLHelper;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.Comments;
import org.apache.poi.xssf.model.SharedStrings;
import org.apache.poi.xssf.model.Styles;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * sax xlsx 解析
 *
 * @author Kevin
 */
public class SaxXlsxExcelReadExecutor<T> extends SaxAbstractExcelReadExecutor<T>
        implements XSSFSheetXMLHandler.SheetContentsHandler {

    private OPCPackage opcPackage;

    private final SharedStrings stringsTable;

    private final List<ReadSheet> readSheets;

    private SheetData<T> sheetData;

    private XlsxReadContext xlsxReadContext;

    private final ReadResult<T> readResult = new ReadResult<>();

    public SaxXlsxExcelReadExecutor(ReadWorkbook readWorkbook) {
        this(readWorkbook, null);
    }

    public SaxXlsxExcelReadExecutor(ReadWorkbook readWorkbook, InputStream decryptedStream) {
        super(readWorkbook);
        readOpcPackage(readWorkbook, decryptedStream);
        try {
            var reader = new XSSFReader(opcPackage);
            stringsTable = reader.getSharedStringsTable();
            readSheets = new ArrayList<>();
            var ite = (XSSFReader.SheetIterator) reader.getSheetsData();
            int index = 0;
            while (ite.hasNext()) {
                var stream = ite.next();
                if (readWorkbook.isParseAllSheet() ||
                        (index >= readWorkbook.getStartSheetIndex() && index <= readWorkbook.getMaxSheetIndex())) {
                    readSheets.add(new ReadSheet(index, ite.getSheetName(), stream, reader.getStylesTable()));
                } else {
                    logger.debug("ignore sheet,sheetIndex:{},sheetName:{}", index, ite.getSheetName());
                }
                index++;
            }
        } catch (Exception e) {
            throw new ExcelReadException(e.getMessage(), e);
        }
    }

    @SneakyThrows
    private void readOpcPackage(ReadWorkbook readWorkbook, InputStream decryptedStream) {
        if (Objects.nonNull(decryptedStream)) {
            this.opcPackage = OPCPackage.open(decryptedStream);
        } else if (Objects.nonNull(readWorkbook.getFile())) {
            this.opcPackage = OPCPackage.open(readWorkbook.getFile());
        } else if (Objects.nonNull(readWorkbook.getInputStream())) {
            this.opcPackage = OPCPackage.open(readWorkbook.getInputStream());
        }
        if (Objects.isNull(opcPackage)) {
            throw new ExcelReadException("opcPackage is null");
        }
    }

    @Override
    public ReadResult<T> execute() {
        try {
            xlsxReadContext = new XlsxReadContext();
            onStart();
            for (var readSheet : readSheets) {
                sheetData = new SheetData<>(readSheet.getSheetIndex(), readSheet.getSheetName());
                xlsxReadContext.setCurrentSheetIndex(readSheet.getSheetIndex());
                xlsxReadContext.setCurrentSheetName(readSheet.getSheetName());
                processSheet(readSheet.getStylesTable(), stringsTable, this, readSheet.getInputStream());
            }
        } catch (Exception e) {
            throw new ExcelReadException(e.getMessage(), e);
        } finally {
            if (Objects.nonNull(opcPackage)) {
                opcPackage.revert();
            }
            var errorLogs = onCompleted();
            if (CollectionUtils.isNotEmpty(errorLogs)) {
                readResult.addErrorLogList(errorLogs);
            }
        }
        return readResult;
    }

    private void processSheet(StylesTable stylesTable, SharedStrings stringsTable,
                              XSSFSheetXMLHandler.SheetContentsHandler saxHandler, InputStream inputStream)
            throws SAXException, ParserConfigurationException, IOException {
        var xmlReader = XMLHelper.newXMLReader();
        var handler = new CountTagXSSFSheetXMLHandler(stylesTable, null, stringsTable, saxHandler,
                new DataFormatter(), !readWorkbook.isOutFormulaValue());
        xmlReader.setContentHandler(handler);
        xmlReader.parse(new InputSource(inputStream));
    }


    private boolean isSheetLimitRow(int rowNum) {
        return readWorkbook.getSheetMaxRow() <= 0 || rowNum <= readWorkbook.getSheetMaxRow();
    }

    @Override
    public void startRow(int rowNum) {
        xlsxReadContext.setRowIndex(rowNum);
        xlsxReadContext.setEmptyRow(true);
        xlsxReadContext.getColumnValueMap().clear();
        if (rowNum != readWorkbook.getTitleRow() && isSheetLimitRow(rowNum)) {
            SaxXlsxExcelReadExecutor.super.onRowStart(xlsxReadContext.getCurrentSheetIndex(),
                    xlsxReadContext.getCurrentSheetName(), rowNum);
        }
    }

    @Override
    public void endRow(int rowNum) {
        if (rowNum == readWorkbook.getTitleRow()) {
            if (CollectionUtils.isEmpty(xlsxReadContext.getTitleList())) {
                var columnValueMap = xlsxReadContext.getColumnValueMap();
                var titles = new ArrayList<Title>(columnValueMap.size());
                var columnField = readWorkbook.getColumnField();
                columnValueMap.forEach((key, value) -> titles.add(new Title(readWorkbook.getTitleRow(), key.intValue(), value, columnField.get(key.intValue()))));
                readResult.setTitleList(titles);
                xlsxReadContext.setTitleList(titles);
            }
        } else if (readWorkbook.getSheetStartRow() <= rowNum && isSheetLimitRow(rowNum) && !xlsxReadContext.isEmptyRow()) {
            T data = null;
            try {
                data = readRow(xlsxReadContext, rowNum);
                sheetData.add(data);
            } catch (InvalidCellReadableExcelException e) {
                sheetData.addErrorLog(new ErrorLog<>(xlsxReadContext.getCurrentSheetIndex(),
                        xlsxReadContext.getCurrentSheetName(), rowNum, e.getTarget(), e.getInvalidCells()));
            } finally {
                SaxXlsxExcelReadExecutor.super.onRowEnd(xlsxReadContext.getCurrentSheetIndex(),
                        xlsxReadContext.getCurrentSheetName(), rowNum, data, xlsxReadContext.getTitleList());
            }
        }
    }

    @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment) {
        if (StringUtils.isNotEmpty(formattedValue)) {
            xlsxReadContext.setEmptyRow(false);
            var cellAddress = new CellAddress(cellReference);
            var row = cellAddress.getRow();
            if (isSheetLimitRow(row)) {
                var column = cellAddress.getColumn();
                if (row < readWorkbook.getTitleRow() || (row != readWorkbook.getTitleRow() && column > xlsxReadContext.getMaxColumnIndex())) {
                    return; // 小于标题行的内容、大于标题行的最大列不解析
                }
                xlsxReadContext.getColumnValueMap().put(column, formattedValue);
            }
        }
    }

    @Override
    public void endSheet() {
        readResult.addSheetData(sheetData);
        SaxXlsxExcelReadExecutor.super.onSheetEnd(xlsxReadContext.getCurrentSheetIndex(), xlsxReadContext.getCurrentSheetName());
    }


    /**
     * XSSFSheetXMLHandler 没有读取Excel 每个sheet总行的事件，这里重写些方法，读取到总行数时，发出通知
     */
    public class CountTagXSSFSheetXMLHandler extends XSSFSheetXMLHandler {

        public CountTagXSSFSheetXMLHandler(
                Styles styles,
                Comments comments,
                SharedStrings strings,
                SheetContentsHandler sheetContentsHandler,
                DataFormatter dataFormatter,
                boolean formulasNotResults) {
            super(styles, comments, strings, sheetContentsHandler, dataFormatter, formulasNotResults);
        }

        @Override
        public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
            if (StringUtils.equals(qName, ExcelXmlConstants.DIMENSION_TAG)
                    || StringUtils.equals(qName, ExcelXmlConstants.X_DIMENSION_TAG)) { //读取总行数
                var d = attributes.getValue(ExcelXmlConstants.ATTRIBUTE_REF);
                var totalStr = d.substring(d.indexOf(":") + 1);
                var c = totalStr.toUpperCase().replaceAll("[A-Z]", "");
                int maxRow = Integer.parseInt(c);// Gets the total number of rows , data may be inaccurate
                xlsxReadContext.setMaxRow(maxRow);
                SaxXlsxExcelReadExecutor.super.onSheetStart(xlsxReadContext.getCurrentSheetIndex(), xlsxReadContext.getCurrentSheetName(), maxRow);
            } else {
                super.startElement(uri, localName, qName, attributes);
            }
        }
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    private static class ReadSheet {

        private int sheetIndex;

        private String sheetName;

        private InputStream inputStream;

        private StylesTable stylesTable;
    }
}
